package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.IOrganizationSettlementDao;
import com.eling.elcms.travel.model.OrganizationSettlement;
import com.eling.elcms.travel.model.view.OrganizationSettlementView;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class OrganizationSettlementDaoHibernate extends GenericDaoHibernate<OrganizationSettlement, Long>
		implements IOrganizationSettlementDao {

	public OrganizationSettlementDaoHibernate() {
		super(OrganizationSettlement.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrganizationSettlementView> queryByService(Date startDate, Date endDate, Long pkOrganization) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT                                                                    ");
		sql.append(" 	tos.pkOrganizationSettlement AS pkOrganizationSettlement,              ");
		sql.append(" 	CASE WHEN tos.paymentType='Advance' THEN '预收' ELSE '预付' END,         ");
		sql.append(" 	tos.content AS content,                                                ");
		sql.append(" 	tos.expectedAmount AS expectedAmount,                                  ");
		sql.append(" 	cog.NAME AS organizationName,                                          ");
		sql.append(" 	tao.NAME AS allianceName,                                              ");
		sql.append(" 	tos.expectedDate AS expectedDate ,                                     ");
		sql.append(" 	cog.pkOrganization as pkOrganization,tao.pkAllianceOrg as pkAllianceOrg , ");
		sql.append(" 	CASE WHEN tos.paymentMethod='Cash' THEN '现金' ELSE 				       ");
		sql.append(" 	(CASE WHEN tos.paymentMethod='WipeCard' THEN '刷卡' ELSE '转账' END) END,");
		sql.append("    tos.isInvoice as isInvoice , tos.invoiceDate as invoiceDate , 		   ");
		sql.append("    tos.description as description , tos.version as version	,tos.status as status   ");
		sql.append(" FROM                                                                      ");
		sql.append(" 	travel_organizationsettlement tos                                      ");
		sql.append(" LEFT JOIN cm_organization cog ON cog.pkOrganization = tos.pkOrganization ");
		sql.append(" LEFT JOIN travel_allianceorg tao ON tao.pkAllianceOrg = tos.pkAllianceOrg ");
		sql.append(" WHERE                                                                     ");
		sql.append(" 	 	tos.pkOrganization = :pkOrganization                               ");
		sql.append(" AND tos.expectedDate>=:startDate AND tos.expectedDate<=:endDate           ");
		sql.append(" AND tos.status='Pending'													   ");
		/*
		 * List<OrganizationSettlement> objList =
		 * getSession().createNativeQuery(sql.toString())
		 * .setParameter("startDate", startDate) .setParameter("endDate",
		 * endDate) .setParameter("pkOrganization", pkOrganization).list();
		 */

		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).setParameter("pkOrganization", pkOrganization).list();

		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		List<OrganizationSettlementView> viewList = new ArrayList<>();
		for (int i = 0; i < objList.size(); i++) {
			OrganizationSettlementView orgSettlement = new OrganizationSettlementView();
			Object[] objects = objList.get(i);
			orgSettlement.setPkOrganizationSettlement(objects[0] == null ? 0 : Long.parseLong(objects[0].toString()));
			orgSettlement.setPaymentType(objects[1] == null ? "" : objects[1].toString());
			orgSettlement.setContent(objects[2] == null ? "" : objects[2].toString());
			orgSettlement.setExpectedAmount(objects[3] == null ? 0.0 : Double.parseDouble(objects[3].toString()));
			orgSettlement.setOrgInfo(objects[4] == null ? "" : objects[4].toString());
			orgSettlement.setAlliInfo(objects[5] == null ? "" : objects[5].toString());
			try {
				orgSettlement.setExpectedDate(df.parse(objects[6].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			orgSettlement.setPkOrganization(objects[7] == null ? 0 : Long.parseLong(objects[7].toString()));
			orgSettlement.setPkAllianceOrg(objects[8] == null ? 0 : Long.parseLong(objects[8].toString()));
			orgSettlement.setPaymentMethod(objects[9] == null ? "" : objects[9].toString());
			if (objects[10] == null ? false : "true".equals(objects[10].toString())) {
				orgSettlement.setIsInvoice(true);
				if(objects[11]!=null){
					try {
						orgSettlement
								.setInvoiceDate(objects[11] == null ? null : df.parse(objects[11].toString()));
					} catch (ParseException e) {
						e.printStackTrace();
					}
				}
			} else {
				orgSettlement.setIsInvoice(false);
			}
			orgSettlement.setDescription(objects[12] == null ? "" : objects[12].toString());
			orgSettlement.setVersion(objects[13] == null ? 0 : Integer.parseInt(objects[13].toString()));
			orgSettlement.setStatus(objects[14] == null?"":objects[14].toString());
			viewList.add(orgSettlement);
		}
		return viewList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<OrganizationSettlementView> queryByAccount(Long pkOrganization, String status, Date startDate,
			Date endDate) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("   tos.pkOrganizationSettlement AS pkOrganizationSettlement,  ");
		sql.append("   ( CASE WHEN tos.paymentType = 'Advance' THEN '预收' ELSE '预付' END ) AS paymentType, ");
		sql.append("   tos.content AS content,  ");
		sql.append("   tos.expectedAmount AS expectedAmount, ");
		sql.append("  cog. NAME AS payer,  ");
		sql.append("  tao. NAME AS payee,  ");
//		sql.append("  tos.expectedDate AS expectedDate,  ");
		sql.append("  tos.realDate,  ");
		sql.append("  tos.realAmount ,  ");
		sql.append("  ( CASE WHEN tos.paymentMethod = 'Cash' THEN '现金' ELSE	(");
		sql.append("  CASE WHEN tos.paymentMethod = 'WipeCard' THEN '刷卡' ELSE ");
		sql.append("  '转账' END )END) AS paymentMethod,  ");
		sql.append("  tos.isInvoice AS isInvoice,  ");
		sql.append("  tos.invoiceDate AS invoiceDate, tos.pkOperator AS pkOperator, tos.description AS description,  ");
		sql.append("  cog.pkOrganization AS pkpayer, tao.pkAllianceOrg AS pkpayee ,tos.status,tos.version ");
		sql.append("  FROM  ");
		sql.append("  travel_organizationsettlement tos  ");
		sql.append("  LEFT JOIN cm_organization cog ON cog.pkOrganization = tos.pkOrganization  ");
		sql.append("  LEFT JOIN travel_allianceorg tao ON tao.pkAllianceOrg= tos.pkAllianceOrg  ");
		sql.append("   WHERE  ");
		sql.append("   tos.pkOrganization = :pkOrganization   ");
		sql.append("   AND tos.expectedDate>=:startDate AND tos.expectedDate<=:endDate  ");
		sql.append("   AND tos.`status` like '%"+status+"' ");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString())
				.setParameter("pkOrganization", pkOrganization).setParameter("startDate", startDate)
				.setParameter("endDate", endDate).list();

		List<OrganizationSettlementView> viewList = new ArrayList<>();

		for (int i = 0; i < objList.size(); i++) {

			OrganizationSettlementView ost = new OrganizationSettlementView();
			Object[] objects = objList.get(i);
			ost.setPkOrganizationSettlement(objects[0] == null ? 0 : Long.parseLong(objects[0].toString()));
			ost.setPaymentType(objects[1] == null ? "" : (objects[1].toString()));
			ost.setContent(objects[2] == null ? "" : (objects[2].toString()));
			ost.setExpectedAmount(objects[3] == null ? 0.0 : new Double(objects[3].toString()));
			ost.setOrgInfo(objects[4] == null ? "" : (objects[4].toString()));
			ost.setAlliInfo(objects[5] == null ? "" : (objects[5].toString()));
			ost.setRealDate(objects[6] == null ? (null) : (Date)objects[6]);
			ost.setRealAmount(objects[7] == null ? 0.0 : new Double(objects[7].toString()));
			ost.setPaymentMethod(objects[8] == null ? "" : (objects[8].toString()));
			ost.setIsInvoice(objects[9] == null ? null : new Boolean(objects[9].toString()));
			ost.setInvoiceDate((Date)objects[10] == null ? null : (Date)objects[10]);
			ost.setPkOperator(objects[11] == null ? 0 : new Long(objects[11].toString()));
			ost.setDescription(objects[12] == null ? "" : (objects[12].toString()));
			ost.setPkOrganization(objects[13] == null ? 0 : Long.parseLong(objects[13].toString()));
			ost.setPkAllianceOrg(objects[14] == null ? 0 : Long.parseLong(objects[14].toString()));
			ost.setStatus(objects[15] == null ? null : objects[15].toString());
			ost.setVersion(objects[16] == null ? null : new Integer( objects[16].toString()));

			viewList.add(ost);
		}
		return viewList;
	}

}
